package by.grsu.crs.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import by.grsu.crs.dao.ConfidentialDataDao;
import by.grsu.crs.model.ConfidentialData;

public class ConfidentialDataDaoImpl implements ConfidentialDataDao {
	private static final String INSERT_NEW_CONFIDENTIAL_DATA
		= "INSERT INTO confidential_data(passport_number, credit_card, name, surname, patronymic, birthdate, phone, country, zip, state, city, street, house, apartment) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	
	private static final String SELECT_ALL_CONFIDENTIAL_DATAS
		= "SELECT id, passport_number, credit_card, name, surname, patronymic, birthdate, phone, country, zip, state, city, street, house, apartment FROM confidential_data";
	
	private static final String SELECT_CONFIDENTIAL_DATA_BY_ID
		= "SELECT id, passport_number, credit_card, name, surname, patronymic, birthdate, phone, country, zip, state, city, street, house, apartment FROM confidential_data WHERE id = ?";
	
	private static final String SELECT_CONFIDENTIAL_DATA_BY_PASSPORT_NUMBER
		= "SELECT id, passport_number, credit_card, name, surname, patronymic, birthdate, phone, country, zip, state, city, street, house, apartment FROM confidential_data WHERE passport_number = ?";
	
	private static final String SELECT_CONFIDENTIAL_DATA_BY_CREDIT_CARD
		= "SELECT id, passport_number, credit_card, name, surname, patronymic, birthdate, phone, country, zip, state, city, street, house, apartment FROM confidential_data WHERE credit_card = ?";
	
	private static final String DELETE_CONFIDENTIAL_DATA_BY_ID
		= "DELETE FROM confidential_data WHERE id = ?";
	
	private static final String UPDATE_CONFIDENTIAL_DATA_BY_ID
		= "UPDATE confidential_data SET passport_number = ?, credit_card = ?, name = ?, surname = ?, patronymic = ?, birthdate = ?, phone = ?, country = ?, zip = ?, state = ?, city = ?, street = ?, house = ?, apartment = ? WHERE id = ?";
	
	private Connection dbConnection;
	private PreparedStatement preparedStatement = null;
	private ResultSet resultSet = null;
	
	public ConfidentialDataDaoImpl(Connection dbConnection) {
		this.dbConnection = dbConnection;
	}
	
	public boolean addConfidentialData(ConfidentialData confData)
			throws SQLException {
		try {
			preparedStatement = dbConnection.prepareStatement(INSERT_NEW_CONFIDENTIAL_DATA);
			preparedStatement.setString(1, confData.getPassportNumber());
			preparedStatement.setInt(2, confData.getCreditCard());
			preparedStatement.setString(3, confData.getName());
			preparedStatement.setString(4, confData.getSurname());
			preparedStatement.setString(5, confData.getPatronymic());
			preparedStatement.setDate(6, (java.sql.Date) confData.getBirthdate());
			preparedStatement.setString(7, confData.getPhone());
			preparedStatement.setString(8, confData.getCountry());
			preparedStatement.setInt(9, confData.getZip());
			preparedStatement.setString(10, confData.getState());
			preparedStatement.setString(11, confData.getCity());
			preparedStatement.setString(12, confData.getStreet());
			preparedStatement.setString(13, confData.getHouse());
			preparedStatement.setString(14, confData.getApartment());
			if (preparedStatement.executeUpdate() == 1) {
				return true;
			}
		} catch(Exception e) {
			//TODO: log this
			e.printStackTrace();
		} finally {
			preparedStatement.close();
		}
		
		return false;
	}

	public List<ConfidentialData> getAllConfidentialDatas() throws SQLException {
		List<ConfidentialData> confDatas = new ArrayList<ConfidentialData>();
		
		try {		
			preparedStatement = dbConnection.prepareStatement(SELECT_ALL_CONFIDENTIAL_DATAS);
			resultSet = preparedStatement.executeQuery();
			
			while (resultSet.next()) {
				confDatas.add(new ConfidentialData(resultSet.getInt("id"), 
						resultSet.getString("passport_number"),
						resultSet.getInt("credit_card"), 
						resultSet.getString("name"), 
						resultSet.getString("surname"), 
						resultSet.getString("patronymic"), 
						resultSet.getDate("birthdate"), 
						resultSet.getString("phone"), 
						resultSet.getString("country"), 
						resultSet.getInt("zip"), 
						resultSet.getString("state"), 
						resultSet.getString("city"), 
						resultSet.getString("street"), 
						resultSet.getString("house"), 
						resultSet.getString("apartment")));
			}
		} catch(Exception e) {
			//TODO: log this
			e.printStackTrace();
		} finally {
			resultSet.close();
			preparedStatement.close();
		}
		
		return confDatas;
	}
	
	public ConfidentialData getConfidentialDataById(int id) throws SQLException {
		ConfidentialData confData = null;
		
		try {		
			preparedStatement = dbConnection.prepareStatement(SELECT_CONFIDENTIAL_DATA_BY_ID);
			preparedStatement.setInt(1, id);
			resultSet = preparedStatement.executeQuery();
			
			resultSet.last();			
			if(resultSet.getRow() != 0) {
				resultSet.first();
				
				String passportNumber = resultSet.getString("passport_number");
				int creditCard = resultSet.getInt("credit_card");
				String name = resultSet.getString("name");
				String surname = resultSet.getString("surname");
				String patronymic = resultSet.getString("patronymic");
				Date birthdate = resultSet.getDate("birthdate");
				String phone = resultSet.getString("phone");
				String country = resultSet.getString("country");
				int zip = resultSet.getInt("zip");
				String state = resultSet.getString("state");
				String city = resultSet.getString("city");
				String street = resultSet.getString("street");
				String house = resultSet.getString("house");
				String apartment = resultSet.getString("apartment");
				
				confData = new ConfidentialData(id, passportNumber, creditCard, name, surname, patronymic, birthdate, phone, country, zip, state, city, street, house, apartment);
			}		
		} catch(Exception e) {
			//TODO: log this
			e.printStackTrace();
		} finally {
			resultSet.close();
			preparedStatement.close();
		}
		
		return confData;
	}

	public ConfidentialData getConfidentialDataByPassportNumber(
			String passportNumber) throws SQLException {
		ConfidentialData confData = null;
		
		try {		
			preparedStatement = dbConnection.prepareStatement(SELECT_CONFIDENTIAL_DATA_BY_PASSPORT_NUMBER);
			preparedStatement.setString(1, passportNumber);
			resultSet = preparedStatement.executeQuery();
			
			resultSet.last();			
			if(resultSet.getRow() != 0) {
				resultSet.first();
				
				int id = resultSet.getInt("id");
				int creditCard = resultSet.getInt("credit_card");
				String name = resultSet.getString("name");
				String surname = resultSet.getString("surname");
				String patronymic = resultSet.getString("patronymic");
				Date birthdate = resultSet.getDate("birthdate");
				String phone = resultSet.getString("phone");
				String country = resultSet.getString("country");
				int zip = resultSet.getInt("zip");
				String state = resultSet.getString("state");
				String city = resultSet.getString("city");
				String street = resultSet.getString("street");
				String house = resultSet.getString("house");
				String apartment = resultSet.getString("apartment");
				
				confData = new ConfidentialData(id, passportNumber, creditCard, name, surname, patronymic, birthdate, phone, country, zip, state, city, street, house, apartment);
			}		
		} catch(Exception e) {
			//TODO: log this
			e.printStackTrace();
		} finally {
			resultSet.close();
			preparedStatement.close();
		}
		
		return confData;
	}

	public ConfidentialData getConfidentialDataByCreditCard(int creditCard)
			throws SQLException {
		ConfidentialData confData = null;
		
		try {		
			preparedStatement = dbConnection.prepareStatement(SELECT_CONFIDENTIAL_DATA_BY_CREDIT_CARD);
			preparedStatement.setInt(1, creditCard);
			resultSet = preparedStatement.executeQuery();
			
			resultSet.last();			
			if(resultSet.getRow() != 0) {
				resultSet.first();
				
				int id = resultSet.getInt("id");
				String passportNumber = resultSet.getString("passport_number");
				String name = resultSet.getString("name");
				String surname = resultSet.getString("surname");
				String patronymic = resultSet.getString("patronymic");
				Date birthdate = resultSet.getDate("birthdate");
				String phone = resultSet.getString("phone");
				String country = resultSet.getString("country");
				int zip = resultSet.getInt("zip");
				String state = resultSet.getString("state");
				String city = resultSet.getString("city");
				String street = resultSet.getString("street");
				String house = resultSet.getString("house");
				String apartment = resultSet.getString("apartment");
				
				confData = new ConfidentialData(id, passportNumber, creditCard, name, surname, patronymic, birthdate, phone, country, zip, state, city, street, house, apartment);
			}		
		} catch(Exception e) {
			//TODO: log this
			e.printStackTrace();
		} finally {
			resultSet.close();
			preparedStatement.close();
		}
		
		return confData;
	}

	public boolean deleteConfidentialDataById(int id) throws SQLException {
		try {
			preparedStatement = dbConnection.prepareStatement(DELETE_CONFIDENTIAL_DATA_BY_ID);
			preparedStatement.setInt(1, id);
			if (preparedStatement.executeUpdate() == 1) {
				return true;
			}
		} catch(Exception e) {
			//TODO: log this
			e.printStackTrace();
		} finally {
			preparedStatement.close();
		}
		
		return false;
	}

	public boolean updateConfidentialDataById(int id, ConfidentialData confData) throws SQLException {
		try {
			preparedStatement = dbConnection.prepareStatement(UPDATE_CONFIDENTIAL_DATA_BY_ID);
			preparedStatement.setString(1, confData.getPassportNumber());
			preparedStatement.setInt(2, confData.getCreditCard());
			preparedStatement.setString(3, confData.getName());
			preparedStatement.setString(4, confData.getSurname());
			preparedStatement.setString(5, confData.getPatronymic());
			preparedStatement.setDate(6, (java.sql.Date) confData.getBirthdate());
			preparedStatement.setString(7, confData.getPhone());
			preparedStatement.setString(8, confData.getCountry());
			preparedStatement.setInt(9, confData.getZip());
			preparedStatement.setString(10, confData.getState());
			preparedStatement.setString(11, confData.getCity());
			preparedStatement.setString(12, confData.getStreet());
			preparedStatement.setString(13, confData.getHouse());
			preparedStatement.setString(14, confData.getApartment());
			if (preparedStatement.executeUpdate() == 1) {
				return true;
			}
		} catch(Exception e) {
			//TODO: log this
			e.printStackTrace();
		} finally {
			preparedStatement.close();
		}
		
		return false;
	}
}
